package bysj.dyz.dao;

import bysj.dyz.bean.Good;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class GoodDao {

    /**
     * 查询所有失物总条数
     * @return
     */

    public int queryCount() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select count(*) from good";
        Long query = queryRunner.query(sql, new ScalarHandler<>());

        return  query.intValue();

    }

    /**
     * 分页查询所有失物
     * @param startPosition
     * @param currentCount
     * @return
     */
    public List<Good> pageselectgod(int startPosition, int currentCount) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="select * from good limit ?,?";
        List<Good> query = queryRunner.query(sql, new BeanListHandler<Good>(Good.class),startPosition,currentCount);

        return  query;
    }


    /**
     * 删除失物
     * @param goodid
     * @throws SQLException
     */
    public boolean deletegood(String goodid) throws SQLException {

        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sqlchild="delete from apply where applygoodid=?";
        int updatechild = queryRunner.update(sqlchild, goodid);
        String sql="delete from good where goodid=?";
        int update = queryRunner.update(sql, goodid);
        if(update>0){
            return  true;
        }else {
            return  false;
        }

    }

    /**
     * 添加失物
     * @param good
     * @return
     * @throws SQLException
     */
    public boolean addGood(Good good) throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql="insert into good values(null,?,?,?,?,?,?,?,null,?,?)";
        int update = queryRunner.update(sql, good.getGoodname(), good.getGoodplace(), good.getGoodtime(), good.getGoodexplain(), good.getGoodtype(), good.getGoodstate(),good.getGoodpicture(), good.getFabucontact(), good.getFabutime());

        if (update>0){
            return true;
        }else {
            return false;
        }
    }
}
